SherbyNet - /page/wikibook/entry.mu

Micron Document

`:top

In `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f, the `!`B100`F9d9TRUNCATE TABLE`f`b`! statement is a `F33f`_`[data manipulation language`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Data_manipulation_language]`_`f (DML)`:cite-ref-1[`F5bf`_`[1`#cite-note-1]`_`f] operation that deletes all rows of a table without causing a triggered action. The result of this operation quickly removes all data from a `F33f`_`[table`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Table_(database)]`_`f, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the `F33f`_`[SQL:2008`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL:2008]`_`f standard, as the optional feature F200, "TRUNCATE TABLE statement".

>>Contents

• `F0af`_`[Behavior`#behavior]`_`f

• `F0af`_`[DML/DDL`#dml-ddl]`_`f

• `F0af`_`[References`#references]`_`f

-─

>>Behavior

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the `F33f`_`[DROP TABLE`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Data_definition_language]`_`f statement.

The `B100`F9d9TRUNCATE TABLE mytable`f`b statement is logically (though not physically) equivalent to the `B100`F9d9`F33f`_`[DELETE`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Delete_(SQL)]`_`f FROM mytable`f`b statement (without a `B100`F9d9WHERE`f`b clause). The following characteristics distinguish `B100`F9d9TRUNCATE TABLE`f`b from `B100`F9d9DELETE`f`b:

• In the `F33f`_`[Oracle Database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Oracle_Database]`_`f, `B100`F9d9TRUNCATE`f`b is implicitly preceded and followed by a `F33f`_`[commit`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Commit_(data_management)]`_`f operation. (This may also be the case in `F33f`_`[MySQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=MySQL]`_`f, when using a transactional storage engine.)

• Typically, `B100`F9d9TRUNCATE TABLE`f`b quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of `F33f`_`[logging`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Transaction_log]`_`f the deletions, as well as the number of `F33f`_`[locks`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Lock_(database)]`_`f acquired. Records removed this way cannot be restored in a rollback operation. Two notable exceptions to this rule are the implementations found in `F33f`_`[PostgreSQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PostgreSQL]`_`f and `F33f`_`[Microsoft SQL Server`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Microsoft_SQL_Server]`_`f, both of which allow `B100`F9d9TRUNCATE TABLE`f`b statements to be committed or rolled back transactionally.

• It is not possible to specify a `B100`F9d9WHERE`f`b clause in a `B100`F9d9TRUNCATE TABLE`f`b statement.

• `B100`F9d9TRUNCATE TABLE`f`b cannot be used when a foreign key references the table to be truncated, since `B100`F9d9TRUNCATE TABLE`f`b statements do not fire `F33f`_`[triggers`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_trigger]`_`f. This could result in inconsistent data because `B100`F9d9ON DELETE`f`b/`B100`F9d9ON UPDATE`f`b triggers would not fire.

• In some computer systems, `B100`F9d9TRUNCATE TABLE`f`b resets the count of an `F33f`_`[Identity column`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Identity_column]`_`f back to the identity's `*seed`*.

>>DML/DDL

The SQL standard classifies TRUNCATE as a `*data change statement`*, synonymous with data manipulation (DML). This aligns with TRUNCATE being logically equivalent to an unconstrained DELETE operation.

However, some documents describe TRUNCATE as a `F33f`_`[data definition language`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Data_definition_language]`_`f (DDL) operation, because TRUNCATE may be seen as a combined DROP+CREATE operation.`:cite-ref-2[`F5bf`_`[2`#cite-note-2]`_`f]

>>References

`:cite-note-1`!1.`! `F0af`_`[↑`#cite-ref-1]`_`f The `F33f`_`[BNF`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Backus-Naur_form]`_`f for SQL:2023 defines TRUNCATE as an SQL data change statement: `:iso-iec-9075-2`a"ISO/IEC 9075 BNF". `*iso.org`*. Retrieved 2024-12-30.

`:cite-note-2`!2.`! `F0af`_`[↑`#cite-ref-2]`_`f For example, MySQL's documentation classifies TRUNCATE as a DDL statement: "MySQL :: MySQL 8.4 Reference Manual :: 15.1.37 TRUNCATE TABLE Statement". `*dev.mysql.com`*. Retrieved 2024-12-30.

`c`F0af`_`[↑ Back to top`#top]`_`f`a